﻿using Dapper;
using duia.Canteenpay.Domain;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace duia.Canteenpay.Repository.Repository
{
    public abstract class BaseRepository<TEntity> where TEntity : BaseEntityId
    {

        public BaseRepository(DbContext context)
        {
            _context = context;
        }

        public IAppUnitOfWork AppUnitOfWork => (IAppUnitOfWork)_context;

        public DbContext _context { get; set; }

        public DbSet<TEntity> Entity => _context.Set<TEntity>();

        public virtual async Task<TEntity> AddAsync(TEntity entity)
        {
            var entityEntry = await _context.Set<TEntity>().AddAsync(entity);
            return entityEntry.Entity;
        }

        public virtual async Task<TEntity> Find(Expression<Func<TEntity, bool>> wherExpression)
        {

            var firstOrDefault = await _context.Set<TEntity>().Where(wherExpression).FirstOrDefaultAsync();
            return firstOrDefault;
        }

        public virtual async Task<IQueryable<TEntity>> FindByCondition(Expression<Func<TEntity, bool>> wherExpression)
        {
            var firstOrDefault = _context.Set<TEntity>().Where(wherExpression);
            return firstOrDefault;
        }

        /// <summary>
        /// 搜索业务对象并排序
        /// </summary>
        /// <typeparam name="Sort"></typeparam>
        /// <param name="searchCriteria"></param>
        /// <param name="sortCriteria"></param>
        /// <param name="ascending"></param>
        /// <returns></returns>
        public virtual async Task<IQueryable<TEntity>> FindByCondition<Sort>(Expression<Func<TEntity, bool>> searchCriteria, Expression<Func<TEntity, Sort>> sortCriteria, bool ascending)
        {
            var data = await this.FindByCondition(searchCriteria);
            if (ascending)
            {
                return data.OrderBy(sortCriteria);
            }
            return data.OrderByDescending(sortCriteria);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="Sort"></typeparam>
        /// <param name="searchCriteria"></param>
        /// <param name="sortCriteria"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="ascending"></param>
        /// <returns></returns>
        public virtual (IQueryable<TEntity>, int) FindPagedByCondition<Sort>(Expression<Func<TEntity, bool>> searchCriteria, Expression<Func<TEntity, Sort>> sortCriteria, int? pageIndex, int? pageSize, bool ascending)
        {
            var enriry = _context.Set<TEntity>().Where(searchCriteria);
            if (ascending)
            {
                enriry = enriry.OrderBy(sortCriteria);
            }
            else
                enriry = enriry.OrderByDescending(sortCriteria);

            int count = enriry.Count();

            var data = enriry.Skip((pageIndex.Value - 1) * pageSize.Value).Take(pageSize.Value);

            return (data, count);

        }

        /// <summary>
        /// 使用SQl分页查询
        /// </summary>
        /// <typeparam name="TObject"></typeparam>
        /// <param name="sqlDistinct"></param>
        /// <param name="sqlStart"></param>
        /// <param name="sqlEnd"></param>
        /// <param name="sort"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="parameters"></param>
        /// <param name="forceOrder"></param>
        /// <returns></returns>
        public virtual IEnumerable<TObject> ExecutePagedQuery<TObject>(string sqlDistinct, string sqlStart, string sqlEnd, Dictionary<string, bool> sort, int? pageIndex = null, int? pageSize = null, List<SqlParameter> parameters = null, bool forceOrder = false)
        {
            if (sort == null || sort.Count < 1)
            {
                throw new Exception("分页查询排序条件没有指定");
            }

            StringBuilder select = new StringBuilder();
            int skipNum = 0;

            //如果是分页
            if (pageIndex.HasValue && pageSize.HasValue)
            {
                select.AppendFormat("SELECT TOP ({0}) * ", pageSize.Value);
                skipNum = pageSize.Value * (pageIndex.Value - 1);
            }
            else
            {
                select.Append("SELECT * ");
            }

            StringBuilder order = new StringBuilder();
            foreach (string key in sort.Keys)
            {
                if (key.Contains(","))
                {
                    string[] arr = key.Split(',');
                    foreach (string a in arr)
                    {
                        if (!string.IsNullOrEmpty(a))
                        {
                            order.AppendFormat("{0} {1},", a, sort[key] == true ? "ASC" : "DESC");
                        }
                    }
                }
                else
                {
                    order.AppendFormat("{0} {1},", key, sort[key] == true ? "ASC" : "DESC");
                }
            }
            order.Remove(order.Length - 1, 1);

            //返回当前页数据
            StringBuilder sql = new StringBuilder();
            sql.Append(select);
            sql.AppendFormat("FROM (SELECT *,row_number() OVER (ORDER BY {0}) AS row_number ", order);
            sql.AppendFormat("FROM ({0}) AS t1) AS t2 ", sqlDistinct);
            sql.AppendFormat("WHERE row_number>{0} ", skipNum);
            sql.AppendFormat("ORDER BY {0} ", order);
            if (forceOrder)
            {
                sql.Append("option (force order)");
            }

            if (parameters != null && parameters.Count > 0)
            {
                SqlParameter[] clonedParameters = new SqlParameter[parameters.Count];
                for (int i = 0, j = parameters.Count; i < j; i++)
                {
                    clonedParameters[i] = (SqlParameter)((ICloneable)parameters[i]).Clone();
                }

                return this.ExecuteQuery<TObject>(string.Format("{0} {1} {2}", sqlStart, sql.ToString(), sqlEnd), clonedParameters);
            }
            else
            {
                return this.ExecuteQuery<TObject>(string.Format("{0} {1} {2}", sqlStart, sql.ToString(), sqlEnd));
            }
        }

        /// <summary>
        /// 使用SQl分页查询
        /// </summary>
        /// <typeparam name="TObject"></typeparam>
        /// <param name="sqlDistinct"></param>
        /// <param name="sqlStart"></param>
        /// <param name="sqlEnd"></param>
        /// <param name="sort"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="parameters"></param>
        /// <param name="forceOrder"></param>
        /// <returns></returns>
        public virtual async Task<IEnumerable<TObject>> ExecutePagedQueryAsync<TObject>(string sqlDistinct, string sqlStart, string sqlEnd, Dictionary<string, bool> sort, int? pageIndex = null, int? pageSize = null, List<SqlParameter> parameters = null, bool forceOrder = false)
        {
            if (sort == null || sort.Count < 1)
            {
                throw new Exception("分页查询排序条件没有指定");
            }

            StringBuilder select = new StringBuilder();
            int skipNum = 0;

            //如果是分页
            if (pageIndex.HasValue && pageSize.HasValue)
            {
                select.AppendFormat("SELECT TOP ({0}) * ", pageSize.Value);
                skipNum = pageSize.Value * (pageIndex.Value - 1);
            }
            else
            {
                select.Append("SELECT * ");
            }

            StringBuilder order = new StringBuilder();
            foreach (string key in sort.Keys)
            {
                if (key.Contains(","))
                {
                    string[] arr = key.Split(',');
                    foreach (string a in arr)
                    {
                        if (!string.IsNullOrEmpty(a))
                        {
                            order.AppendFormat("{0} {1},", a, sort[key] == true ? "ASC" : "DESC");
                        }
                    }
                }
                else
                {
                    order.AppendFormat("{0} {1},", key, sort[key] == true ? "ASC" : "DESC");
                }
            }
            order.Remove(order.Length - 1, 1);

            //返回当前页数据
            StringBuilder sql = new StringBuilder();
            sql.Append(select);
            sql.AppendFormat("FROM (SELECT *,row_number() OVER (ORDER BY {0}) AS row_number ", order);
            sql.AppendFormat("FROM ({0}) AS t1) AS t2 ", sqlDistinct);
            sql.AppendFormat("WHERE row_number>{0} ", skipNum);
            sql.AppendFormat("ORDER BY {0} ", order);
            if (forceOrder)
            {
                sql.Append("option (force order)");
            }

            if (parameters != null && parameters.Count > 0)
            {
                SqlParameter[] clonedParameters = new SqlParameter[parameters.Count];
                for (int i = 0, j = parameters.Count; i < j; i++)
                {
                    clonedParameters[i] = (SqlParameter)((ICloneable)parameters[i]).Clone();
                }

                return await this.ExecuteQueryAsync<TObject>(string.Format("{0} {1} {2}", sqlStart, sql.ToString(), sqlEnd), clonedParameters);
            }
            else
            {
                return await this.ExecuteQueryAsync<TObject>(string.Format("{0} {1} {2}", sqlStart, sql.ToString(), sqlEnd));
            }
        }

        /// <summary>
        /// 执行查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parame"></param>
        /// <returns></returns>
        public virtual async Task<IEnumerable<T>> ExecuteQueryAsync<T>(string sql, params SqlParameter[] parame)
        {

            //UnitOfWork.Database.GetDbConnection().ConnectionString
            using (var connection = new SqlConnection(_context.Database.GetConnectionString()))
            {
                connection.Open();
                if (parame.Length > 0)
                {
                    return await connection.QueryAsync<T>(sql, parame);
                }
                return await connection.QueryAsync<T>(sql);
            }
        }

        /// <summary>
        /// 执行查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parame"></param>
        /// <returns></returns>
        public virtual IEnumerable<T> ExecuteQuery<T>(string sql, params SqlParameter[] parame)
        {


            //UnitOfWork.Database.GetDbConnection().ConnectionString
            using (var connection = new SqlConnection(_context.Database.GetConnectionString()))
            {
                connection.Open();
                if (parame.Length > 0)
                {
                    return connection.Query<T>(sql, parame);
                }
                return connection.Query<T>(sql);
            }
        }
        public virtual void RemoveAsync(TEntity entity)
        {
            _context.Set<TEntity>().Remove(entity);
        }

        public virtual void RemoveAsync(Guid[] parameKeyId)
        {
            var entity = _context.Set<TEntity>();

            entity.RemoveRange(entity.Where(x => parameKeyId.Contains(x.KeyId)));

        }

        public virtual void UpdateAsync(TEntity entity)
        {
            _context.Set<TEntity>().Update(entity);
        }

    }
}
